code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/migrations/0116_additional_referral_set_stats split_factors.sql (about)

     1  -- +goose Up
     2  DROP TABLE IF EXISTS referral_set_stats;
     3  
     4  -- create the new version of referral_set_stats
     5  CREATE TABLE IF NOT EXISTS referral_set_stats
     6  (
     7      set_id bytea NOT NULL,
     8      at_epoch bigint NOT NULL,
     9      referral_set_running_notional_taker_volume text NOT NULL,
    10      referees_stats jsonb NOT NULL,
    11      vega_time timestamp with time zone NOT NULL,
    12      rewards_multiplier text NOT NULL DEFAULT '0',
    13      was_eligible boolean NOT NULL DEFAULT true,
    14      referrer_taker_volume text NOT NULL DEFAULT '0',
    15      reward_factors jsonb NOT NULL,
    16      rewards_factors_multiplier jsonb NOT NULL,
    17      CONSTRAINT referral_set_stats_pkey PRIMARY KEY (vega_time, set_id)
    18  );
    19  
    20  SELECT create_hypertable('referral_set_stats', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    21  
    22  
    23  UPDATE volume_discount_stats
    24  SET parties_volume_discount_stats = (
    25    SELECT jsonb_agg(
    26      jsonb_set(
    27        party_stats,
    28        '{discount_factors}',
    29        jsonb_build_object(
    30          'infrastructure_discount_factor', party_stats->>'discount_factor',
    31          'liquidity_discount_factor', party_stats->>'discount_factor',
    32          'maker_discount_factor', party_stats->>'discount_factor'
    33        )
    34      )
    35    )
    36    FROM jsonb_array_elements(parties_volume_discount_stats) AS party_stats
    37  );
    38  
    39  
    40  alter table trades ADD COLUMN buyer_buy_back_fee HUGEINT NOT NULL DEFAULT(0),
    41                     ADD COLUMN buyer_treasury_fee HUGEINT NOT NULL DEFAULT(0),
    42                     ADD COLUMN buyer_high_volume_maker_fee HUGEINT NOT NULL DEFAULT(0),
    43                     ADD COLUMN seller_buy_back_fee HUGEINT NOT NULL DEFAULT(0),
    44                     ADD COLUMN seller_treasury_fee HUGEINT NOT NULL DEFAULT(0),
    45                     ADD COLUMN seller_high_volume_maker_fee HUGEINT NOT NULL DEFAULT(0);
    46  
    47  -- create a new volume rebate record when a new volume rebate program is created,
    48  -- updated, or ended so that we keep an audit trail, just in case.
    49  -- We create it as a hypertable and set a retention policy to make sure
    50  -- old and redundant data is removed in due course.
    51  create table if not exists volume_rebate_programs
    52  (
    53      id                       bytea                    not null,
    54      version                  int                      not null,
    55      benefit_tiers            jsonb,
    56      end_of_program_timestamp timestamp with time zone not null,
    57      window_length            int                      not null,
    58      vega_time                timestamp with time zone not null,
    59      ended_at                 timestamp with time zone,
    60      seq_num                  bigint                   not null,
    61      primary key (vega_time, seq_num)
    62  );
    63  
    64  select create_hypertable('volume_rebate_programs', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    65  
    66  -- simplify volume rebate retrieval using a view that provides the latest volume rebate information.
    67  create view current_volume_rebate_program as
    68  (
    69  select *
    70  from volume_rebate_programs
    71  order by vega_time desc, seq_num desc
    72  limit 1 -- there should only be 1 volume rebate program running at any time, so just get the last record.
    73      );
    74  
    75  create table volume_rebate_stats
    76  (
    77      at_epoch                      bigint                   not null,
    78      parties_volume_rebate_stats jsonb                    not null,
    79      vega_time                     timestamp with time zone not null,
    80      primary key (at_epoch, vega_time)
    81  );
    82  
    83  select create_hypertable('volume_rebate_stats', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    84  
    85  ALTER TYPE proposal_error ADD VALUE IF NOT EXISTS 'PROPOSAL_ERROR_INVALID_VOLUME_REBATE_PROGRAM';
    86  
    87  -- +goose Down
    88  
    89  drop table if exists volume_rebate_stats;
    90  drop view if exists current_volume_rebate_program;
    91  drop table if exists volume_rebate_programs;
    92  
    93  -- drop the new schema, restore the old one.
    94  DROP TABLE IF EXISTS referral_set_stats;
    95  
    96  -- create the new version of referral_set_stats
    97  CREATE TABLE IF NOT EXISTS referral_set_stats
    98  (
    99      set_id bytea NOT NULL,
   100      at_epoch bigint NOT NULL,
   101      referral_set_running_notional_taker_volume text NOT NULL,
   102      referees_stats jsonb NOT NULL,
   103      vega_time timestamp with time zone NOT NULL,
   104      rewards_multiplier text NOT NULL DEFAULT '0',
   105      was_eligible boolean NOT NULL DEFAULT true,
   106      referrer_taker_volume text NOT NULL DEFAULT '0',
   107      reward_factor text NOT NULL DEFAULT '0',
   108      rewards_factor_multiplier text NOT NULL DEFAULT '0',
   109      CONSTRAINT referral_set_stats_pkey PRIMARY KEY (vega_time, set_id)
   110  );
   111  
   112  SELECT create_hypertable('referral_set_stats', 'vega_time', chunk_time_interval => INTERVAL '1 day');
   113  
   114  alter table trades DROP COLUMN buyer_buy_back_fee,
   115                     DROP COLUMN buyer_treasury_fee,
   116                     DROP COLUMN seller_buy_back_fee,
   117                     DROP COLUMN seller_treasury_fee,
   118                     DROP COLUMN buyer_high_volume_maker_fee,
   119                     DROP COLUMN seller_high_volume_maker_fee;